﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Windows;
using OfficeOpenXml;

namespace GPVC
{
    class ExcelIO
    {
        public struct ExcelInfo
        {
            public string fileName;
            public ExcelPackage excelObject;
            public int countWorkSheet;
            public List<Tuple<string, int[]>> workSheetInfo;
            public List<string> defaultWorkSheetField;
            public int errInfo;
        }

        private ExcelInfo excelInfo;

        private ExcelPackage OpenExcel(string filePath, string mode = "r")
        {
            ExcelPackage excelPackage = null;
            try
            {
                FileInfo fileInfo = new FileInfo(filePath);
                excelPackage = new ExcelPackage(fileInfo);
                //if (fileInfo.Exists)
                //    excelPackage = new ExcelPackage(fileInfo);
                //else
                //    MessageBox.Show("Can't find this file!", "Error", MessageBoxButtons.OK);
            }
            catch (Exception errInfo)
            {
                excelInfo.errInfo = -1;
                MessageBox.Show(errInfo.Message, "Error", MessageBoxButton.OK);
            }
            //
            if (mode == "w")
            {
                for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
                {
                    excelPackage.Workbook.Worksheets.Delete(i + 1);
                }
            }
            //
            return excelPackage;
        }

        public ExcelInfo GetExcelFileInfo(string filePath)
        {
            int[] counRowCol = { 0, 0 };
            //
            var excelPackage = OpenExcel(filePath);
            var countWorkSheet = excelPackage.Workbook.Worksheets.Count;
            //
            excelInfo.fileName = Path.GetFileName(filePath);
            excelInfo.countWorkSheet = excelPackage.Workbook.Worksheets.Count;
            excelInfo.excelObject = excelPackage;
            excelInfo.workSheetInfo = new List<Tuple<string, int[]>>();
            //
            for (var i = 0; i < countWorkSheet; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheet.Dimension == null)
                {
                    Tuple<string, int[]> workSheetInfo = new Tuple<string, int[]>(tmpWorkSheetName, counRowCol);
                    excelInfo.workSheetInfo.Add(workSheetInfo);
                }
                else
                {
                    counRowCol[0] = tmpWorkSheet.Dimension.End.Row;
                    counRowCol[1] = tmpWorkSheet.Dimension.End.Column;
                    Tuple<string, int[]> workSheetInfo = new Tuple<string, int[]>(tmpWorkSheetName, counRowCol);
                    excelInfo.workSheetInfo.Add(workSheetInfo);
                }
            }
            excelInfo.defaultWorkSheetField = ReadExcelRow(excelPackage, excelInfo.workSheetInfo[0].Item1, 0);
            return excelInfo;
        }

        public List<string> ReadExcelRow(string filePath, string workSheetName, int row)
        {
            //
           var excelData = new List<string>();
            //
            var excelPackage = OpenExcel(filePath);
            //
            for (var k = 0; k < excelPackage.Workbook.Worksheets.Count; k++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[k];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (row > tmpWorkSheetMaxRow)
                    {
                        MessageBox.Show("Row is out of working sheet!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 1; j <= tmpWorkSheetMaxCol; j++)
                        {
                            excelData.Add(tmpWorkSheet.Cells[1, j].Value.ToString());
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public List<string> ReadExcelRow(ExcelPackage excelPackage, string workSheetName, int row)
        {
            //
            var excelData = new List<string>();
            //
            for (var k = 0; k < excelPackage.Workbook.Worksheets.Count; k++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[k];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (row > tmpWorkSheetMaxRow)
                    {
                        MessageBox.Show("Row is out of working sheet!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 1; j <= tmpWorkSheetMaxCol; j++)
                        {
                            excelData.Add(tmpWorkSheet.Cells[1, j].Value.ToString());
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public int QueryFieldIndex(string filePath, string workSheetName, string field)
        {
            var rowData = ReadExcelRow(filePath, workSheetName, 0);
            var fieldIndex = rowData.IndexOf(field);
            //
            return fieldIndex + 1;
        }

        public int QueryFieldIndex(ExcelPackage excelPackage, string workSheetName, string field)
        {
            var rowData = ReadExcelRow(excelPackage, workSheetName, 0);
            var fieldIndex = rowData.IndexOf(field);
            //
            return fieldIndex + 1;
        }

        public List<List<double>> ReadExcel(string filePath, string workSheetName, int xIndex, int yIndex)
        {
            //
            List<List<double>> excelData = new List<List<double>>();
            //
            var excelPackage = OpenExcel(filePath);
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xIndex > tmpWorkSheetMaxCol || yIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public List<List<double>> ReadExcel(string filePath, string workSheetName, string xField, string yField)
        {
            //
            var excelData = new List<List<double>>();
            //
            var excelPackage = OpenExcel(filePath);
            //
            var xFieldIndex = QueryFieldIndex(excelPackage, workSheetName, xField);
            var yFieldIndex = QueryFieldIndex(excelPackage, workSheetName, yField);
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xFieldIndex > tmpWorkSheetMaxCol || yFieldIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xFieldIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yFieldIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public List<List<double>> ReadExcel(ExcelPackage excelPackage, string workSheetName, int xIndex, int yIndex)
        {
            List<List<double>> excelData = new List<List<double>>();
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xIndex > tmpWorkSheetMaxCol || yIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public List<List<double>> ReadExcel(ExcelPackage excelPackage, string workSheetName, string xField, string yField)
        {
            List<List<double>> excelData = new List<List<double>>();
            //
            var xFieldIndex = QueryFieldIndex(excelPackage, workSheetName, xField);
            var yFieldIndex = QueryFieldIndex(excelPackage, workSheetName, yField);
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xFieldIndex > tmpWorkSheetMaxCol || yFieldIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xFieldIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yFieldIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public void WriteExcel(string filePath, List<List<double>> outData, string workSheetName)
        {
            string mode = "w";
            var excelPackage = OpenExcel(filePath, mode);
            //
            var ws = excelPackage.Workbook.Worksheets.Add(workSheetName);
            //
            ws.Cells[1, 1].Value = "ID";
            ws.Cells[1, 2].Value = "Lon";
            ws.Cells[1, 3].Value = "Lat";
            //
            for (var i = 1; i <= outData[0].Count - 3; i++)
            {
                ws.Cells[1, i + 3].Value = "Band" + i.ToString();
            }
            //
            var j = 2;
            foreach (List<double> lineData in outData)
            {
                for (var k = 1; k <= lineData.Count; k++)
                {
                    ws.Cells[j, k].Value = lineData[k - 1];
                }
                j++;
            }
            try
            {
                excelPackage.SaveAs(new FileInfo(filePath));
            }
            catch (Exception errInfo)
            {
                MessageBox.Show(errInfo.Message, "Error", MessageBoxButton.OK);
            }
        }


    }
}
